package visiopuce.DAO;

import java.math.BigDecimal;
import java.sql.Date;
import java.util.List;

import org.hibernate.Query;

import visiopuce.HibernateUtil;
import visiopuce.objets.Cotation;

public class CotationDAOImpl extends GenericDAOImpl<Cotation, BigDecimal> implements CotationDAO {

	/** Constructeur priv� */
	private CotationDAOImpl() {
	}

	/** Instance unique non pr�initialis�e */
	private static CotationDAOImpl INSTANCE = null;

	/** Point d'acc�s pour l'instance unique du singleton */
	public static CotationDAO getInstance() {
		if (INSTANCE == null) {
			INSTANCE = new CotationDAOImpl();
		}
		return INSTANCE;
	}

	public List<Cotation> getCotationByidPersonne(Integer idPersonne) {
		List<Cotation> cotations = null;
		String sql = "SELECT c FROM Cotation c WHERE c.personne.idPersonne = :idPersonne";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPersonne", idPersonne);
		cotations = findMany(query);
		return cotations;
	}

	@Override
	public List<Cotation> getCotationByPrelevementAndLibelle(Integer idPrelevement, String cot) {
		List<Cotation> cotations = null;
		String sql = "SELECT c FROM Cotation c WHERE c.prelevement.idPrelevement = :idPrelevement AND c.typeCotation.libelle = :cot";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPrelevement", idPrelevement).setParameter("cot", cot);
		cotations = findMany(query);
		return cotations;
	}

	@Override
	public void deleteAllByidPersonne(Integer idPersonne) {
		String sql = "DELETE FROM Cotation c WHERE c.personne.idPersonne = :idPersonne";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPersonne", idPersonne);
		int rowCount = query.executeUpdate();
	}

	@Override
	public List<Cotation> getCotationByidPrelevement(Integer idPrelevement) {
		List<Cotation> cotations = null;
		String sql = "SELECT c FROM Cotation c WHERE c.prelevement.idPrelevement = :idPrelevement";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPrelevement", idPrelevement);
		cotations = findMany(query);
		return cotations;
	}

	@Override
	public List<Cotation> getCotationByidAnalyse(Integer idAnalyse) {
		List<Cotation> cotations = null;
		String sql = "SELECT c FROM Cotation c WHERE c.analyse.idAnalyse = :idAnalyse";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idAnalyse", idAnalyse);
		cotations = findMany(query);
		return cotations;
	}

	@Override
	public List<Cotation> getCotationByAnnee(int annee) {
		List<Cotation> cotations = null;
		String sql = "SELECT c FROM Cotation c WHERE c.date between :annee and :annee2";
		Query query = HibernateUtil.getSession().createQuery(sql);
		query.setDate("annee", Date.valueOf(annee - 1 + "-12-31"));
		query.setDate("annee2", Date.valueOf(annee + "-12-31"));
		cotations = findMany(query);
		return cotations;
	}

	@Override
	public void save(Cotation cotation) {
		String sql = "SELECT c FROM Cotation c WHERE c.typeCotation = :typeCotation ";
		if (cotation.getPersonne() != null) {
			sql += " AND c.personne.idPersonne = :idPersonne";
		}
		if (cotation.getPrelevement() != null) {
			sql += " AND c.prelevement.idPrelevement = :idPrelevement";
		}
		if (cotation.getAnalyse() != null) {
			sql += " AND c.analyse.idAnalyse = :idAnalyse";
		}
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("typeCotation", cotation.getTypeCotation());
		if (cotation.getPersonne() != null) {
			query.setParameter("idPersonne", cotation.getPersonne().getIdPersonne());
		}
		if (cotation.getPrelevement() != null) {
			query.setParameter("idPrelevement", cotation.getPrelevement().getIdPrelevement());
		}
		if (cotation.getAnalyse() != null) {
			query.setParameter("idAnalyse", cotation.getAnalyse().getIdAnalyse());
		}
		List<Cotation> cotationTrouvee = findMany(query);
		if (cotationTrouvee == null || cotationTrouvee.size() == 0) {
			super.save(cotation);
		} else {
			for (Cotation cotation2 : cotationTrouvee) {
				cotation2.setDate(cotation.getDate());
				cotation2.setEnvoyer(cotation.getEnvoyer());
				super.save(cotation2);
			}

		}

	}

	@Override
	public List<Cotation> getCotationByNumSejourNonEnvoyee(String numSejour) {
		List<Cotation> cotations = null;
		String sql = "SELECT distinct c FROM Cotation c WHERE (c.envoyer is null) and ((c.prelevement is not null and c.prelevement.numSejour like :numSejour1) )";
		// String sql =
		// "SELECT distinct c FROM Cotation c WHERE  c.envoyer is null and (c.prelevement is not null and c.prelevement.numSejour like :numSejour1)";
		// String sql =
		// "SELECT distinct c FROM Cotation c WHERE  (c.analyse is not null and c.analyse.prelevement.numSejour like :numSejour2)";
		Query query = HibernateUtil.getSession().createQuery(sql);
		query.setParameter("numSejour1", numSejour);
		// query.setParameter("numSejour2", numSejour);
		cotations = findMany(query);
		
		List<Cotation> cotations2 = null;
		String sql2 = "SELECT distinct c FROM Cotation c WHERE (c.envoyer is null) and ((c.prelevement is null and c.analyse is not null and c.analyse.prelevement.numSejour like :numSejour2) )";
		// String sql =
		// "SELECT distinct c FROM Cotation c WHERE  c.envoyer is null and (c.prelevement is not null and c.prelevement.numSejour like :numSejour1)";
		// String sql =
		// "SELECT distinct c FROM Cotation c WHERE  (c.analyse is not null and c.analyse.prelevement.numSejour like :numSejour2)";
		Query query2 = HibernateUtil.getSession().createQuery(sql2);
		query2.setParameter("numSejour2", numSejour);
		// query.setParameter("numSejour2", numSejour);
		cotations2 = findMany(query2);
		
		cotations.addAll(cotations2);
		return cotations;
	}

	@Override
	public List<Cotation> getCotationByNumSejour(String numSejour) {
		List<Cotation> cotations = null;
		String sql = "SELECT distinct c FROM Cotation c WHERE ((c.prelevement is not null and c.prelevement.numSejour = :numSejour) or (c.prelevement is null and c.analyse is not null and c.analyse.prelevement.numSejour = :numSejour)) ";
		Query query = HibernateUtil.getSession().createQuery(sql);
		query.setParameter("numSejour", numSejour);
		cotations = findMany(query);
		return cotations;
	}
}